using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using emp.IDAL;
using emp.Model;
using System.Data.SqlClient;
using System.Data;
using emp.Utility;

namespace emp.SqlServerDAL
{
    /// <summary>
    /// author      
    /// </summary>
    public class CellType:ICellType
    {
        #region ICellType Members

        // Static constants
        private const string SQL_SELECT_LIST = "SELECT cla_id,cla_name,cla_desc FROM cell_category order by cla_id desc";
        private const string SQL_SELECT_INFO = "SELECT cla_id,cla_name,cla_desc FROM cell_category where cla_id=@CellTypeId";
        private const string SQL_INSERT = " exec p_celltype_save @CellTypeName, @CellTypeDesc ";
        private const string SQL_UPDATE = " exec p_celltype_update @CellTypeId, @CellTypeName, @CellTypeDesc";//

        private const string PARM_CellType_ID = "@CellTypeId";
        private const string PARM_CellType_Name = "@CellTypeName";
        private const string PARM_CellType_Desc = "@CellTypeDesc";
        private const string PARM_ISDEL = "@IsDel";

        public int Insert(CellTypeInfo myCellTypeInfo)
        {
            SqlParameter[] cellTypeParms = GetParameters();
            SetParameters(cellTypeParms, myCellTypeInfo);

            int returnValue = 0;
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {

                        returnValue = SQLHelper.ExecuteNonQuery(trans, CommandType.Text, SQL_INSERT, cellTypeParms);
                        trans.Commit();
                    }
                    catch (SqlException e)
                    {
                        string sMethod = "emp.SqlServerDAL.CellType.Insert()";
                        EventsLog.WriteLog(e, sMethod);
                        trans.Rollback();
                        //System.Console.WriteLine(err.Message);
                    }
                }
                return returnValue;
            }

        }

        public int Update(CellTypeInfo myCellTypeInfo)
        {
            SqlParameter[] cellTypeParms = GetParametersID();

            SetParametersID(cellTypeParms, myCellTypeInfo);

            int returnValue = 0;
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        returnValue = SQLHelper.ExecuteNonQuery(trans, CommandType.Text, SQL_UPDATE, cellTypeParms);

                        trans.Commit();
                    }
                    catch (Exception e)
                    {
                        string sMethod = "emp.SqlServerDAL.CellType.Update()";
                        EventsLog.WriteLog(e, sMethod);
                        trans.Rollback();
                        //return returnValue;
                        //throw;
                    }
                }
            }

            return returnValue;
        }

        public int Delete(int iCellTypeID)
        {

            int returnValue = 0;
            string SQL_DELETE_CAM = "delete cell_category where cla_id=" + iCellTypeID;
            SqlParameter[] Parms = new SqlParameter[] { };
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(trans, CommandType.Text, SQL_DELETE_CAM, Parms);
                        trans.Commit();
                        returnValue = 1;
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }

            return returnValue;
        }

        public IList getCellTypeList(string sIsDel)
        {
            IList myCellTypeList = new ArrayList();
            SqlParameter parm = new SqlParameter(PARM_ISDEL, SqlDbType.Char, 1);
            parm.Value = sIsDel;

            //Execute a query to read the products
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_SELECT_LIST, parm))
            {
                while (rdr.Read())
                {
                    CellTypeInfo myCellTypeInfo = new CellTypeInfo(rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2));
                    myCellTypeList.Add(myCellTypeInfo);
                }
            }

            return myCellTypeList;
        }

        public IList getCellTypeInfo(string sIsDel, int iCellTypeID)
        {
            IList myCellTypeList = new ArrayList();
            SqlParameter[] parm = new SqlParameter[]{
                new SqlParameter(PARM_CellType_ID, SqlDbType.Int, 4,"cell_type_uid"),
                new SqlParameter(PARM_ISDEL, SqlDbType.Char, 1,"is_del")
                };
            parm[0].Value = iCellTypeID;
            parm[1].Value = sIsDel;

            //Execute a query to read the products
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_SELECT_INFO, parm))
            {
                while (rdr.Read())
                {
                    CellTypeInfo myCellTypeInfo = new CellTypeInfo(rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2));
                    myCellTypeList.Add(myCellTypeInfo);
                }
            }

            return myCellTypeList;
        }

        /// <summary>
        /// An internal function to get the database parameters
        /// </summary>
        /// <returns>Parameter array</returns>
        private static SqlParameter[] GetParameters()
        {
            SqlParameter[] parms = SQLHelper.GetCachedParameters(SQL_INSERT);

            if (parms == null)
            {
                parms = new SqlParameter[] {
					new SqlParameter(PARM_CellType_Name, SqlDbType.VarChar, 100),
					new SqlParameter(PARM_CellType_Desc, SqlDbType.VarChar, 500)};

                SQLHelper.CacheParameters(SQL_INSERT, parms);
            }

            return parms;
        }

        /// <summary>
        /// An internal function to get the database parameters
        /// </summary>
        /// <returns>Parameter array</returns>
        private static SqlParameter[] GetParametersID()
        {
            SqlParameter[] parms = SQLHelper.GetCachedParameters(SQL_UPDATE);

            if (parms == null)
            {
                parms = new SqlParameter[] {
					new SqlParameter(PARM_CellType_ID, SqlDbType.Int, 4),
					new SqlParameter(PARM_CellType_Name, SqlDbType.VarChar, 100),
					new SqlParameter(PARM_CellType_Desc, SqlDbType.VarChar, 500)};

                SQLHelper.CacheParameters(SQL_UPDATE, parms);
            }

            return parms;
        }

        /// <summary>
        /// An internal function to bind values parameters 
        /// </summary>
        /// <param name="parms">Database parameters</param>
        /// <param name="myCelltypeInfo">Values to bind to parameters</param>
        private void SetParameters(SqlParameter[] parms, CellTypeInfo myCellTypeInfo)
        {
            parms[0].Value = myCellTypeInfo.CellTypeName;
            parms[1].Value = myCellTypeInfo.CellTypeDesc;
        }

        /// <summary>
        /// An internal function to bind values parameters 
        /// </summary>
        /// <param name="parms">Database parameters</param>
        /// <param name="myCelltypeInfo">Values to bind to parameters</param>
        private void SetParametersID(SqlParameter[] parms, CellTypeInfo myCellTypeInfo)
        {
            parms[0].Value = myCellTypeInfo.CellTypeID;
            parms[1].Value = myCellTypeInfo.CellTypeName;
            parms[2].Value = myCellTypeInfo.CellTypeDesc;
        }

        #endregion
    }
}
